namespace CISLib
{
    using System;
    using System.Collections;
    using System.Data;
    using System.Data.SqlClient;

    public class ManageProvince
    {
        public static Province[] GetAllProvince()
        {
            SqlDataReader MyReader = null;
            ArrayList provinceList = new ArrayList();
            string strSQL = "Select RegionId from TB_Region where areaId = '06BA8C26-B1C9-4415-A6EC-1CF5CE99BF15' and ParentId='00000000-0000-0000-0000-000000000000' and regionid <> '00000000-0000-0000-0000-000000000000' ORDER BY RegionSCName";
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.DBSetSelect(strSQL);
            MyDB.DBExcuteReader();
            MyReader = MyDB.DBReader;
            while (MyReader.Read())
            {
                try
                {
                    Province province = new Province();
                    province = GetProvinceViaId(MyReader.GetGuid(0));
                    provinceList.Add(province);
                    continue;
                }
                catch
                {
                    continue;
                }
            }
            MyDB.DBClose();
            return (Province[]) provinceList.ToArray(typeof(Province));
        }

        public static SqlDataReader GetProvince()
        {
            string strSQL = "Select * from TB_Region where ParentId='00000000-0000-0000-0000-000000000000'  ORDER BY areaid, RegionSCName";
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.DBSetSelect(strSQL);
            MyDB.DBExcuteReader();
            return MyDB.DBReader;
        }

        public static SqlDataReader GetProvince(Guid areaId)
        {
            string strSQL = "Select * from TB_Region where areaId = @AreaId and ParentId='00000000-0000-0000-0000-000000000000' ORDER BY RegionSCName";
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.AddParameters(areaId, "AreaId", SqlDbType.UniqueIdentifier);
            MyDB.DBSetSelect(strSQL);
            MyDB.DBExcuteReader();
            return MyDB.DBReader;
        }

        public static Province[] GetProvinceAryViaUserId(Guid userId)
        {
            SqlDataReader MyReader = null;
            ArrayList provinceList = new ArrayList();
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.AddParameters(userId, "UserId", SqlDbType.UniqueIdentifier);
            string strSQL = "Select TB_UserRegion.RegionId from TB_UserRegion left outer join TB_Region on TB_UserRegion.RegionId = TB_Region.RegionId  where UserId = @UserId order by TB_Region.RegionName";
            MyDB.DBSetSelect(strSQL);
            MyDB.DBExcuteReader();
            MyReader = MyDB.DBReader;
            while (MyReader.Read())
            {
                try
                {
                    Province province = new Province();
                    province = GetProvinceViaId(MyReader.GetGuid(0));
                    provinceList.Add(province);
                    continue;
                }
                catch
                {
                    continue;
                }
            }
            MyDB.DBClose();
            return (Province[]) provinceList.ToArray(typeof(Province));
        }

        public static SqlDataReader GetProvinceNoAll()
        {
            string strSQL = "Select * from TB_Region where ParentId='00000000-0000-0000-0000-000000000000'and regionId <>'00000000-0000-0000-0000-000000000000'  ORDER BY areaid, RegionSCName";
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.DBSetSelect(strSQL);
            MyDB.DBExcuteReader();
            return MyDB.DBReader;
        }

        public static SqlDataReader GetProvinceNoAllViaAreaId(Guid areaId)
        {
            string strSQL = "Select * from TB_Region where ParentId='00000000-0000-0000-0000-000000000000'and regionId <>'00000000-0000-0000-0000-000000000000' and AreaId=@AreaId  ORDER BY areaid, RegionSCName";
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.AddParameters(areaId, "AreaId", SqlDbType.UniqueIdentifier);
            MyDB.DBSetSelect(strSQL);
            MyDB.DBExcuteReader();
            return MyDB.DBReader;
        }

        public static Province GetProvinceViaId(Guid id)
        {
            SqlDataReader MyReader = null;
            Province province = new Province();
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            string strSQL = "select * from tb_Region where RegionId = @RegionId";
            MyDB.AddParameters(id, "RegionId", SqlDbType.UniqueIdentifier);
            MyDB.DBSetSelect(strSQL);
            MyDB.DBExcuteReader();
            MyReader = MyDB.DBReader;
            if (MyReader.Read())
            {
                province.ProvinceId = MyReader.GetGuid(0);
                province.ProvinceName = MyReader.GetString(1);
                province.ProvinceSCName = MyReader.GetString(2);
                province.ParentId = MyReader.GetGuid(3);
                province.AreaId = MyReader.GetGuid(5);
            }
            MyDB.DBClose();
            return province;
        }

        public static void InsertNewProvince(string name, string scName, Guid areaId)
        {
            Guid id = Guid.NewGuid();
            Guid parentId = new Guid("00000000-0000-0000-0000-000000000000");
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.AddParameters(name, "RegionName", SqlDbType.VarChar);
            MyDB.AddParameters(scName, "RegionSCName", SqlDbType.VarChar);
            MyDB.AddParameters(id, "RegionId", SqlDbType.UniqueIdentifier);
            MyDB.AddParameters(parentId, "ParentId", SqlDbType.UniqueIdentifier);
            MyDB.AddParameters(areaId, "AreaId", SqlDbType.UniqueIdentifier);
            string strSQL = "Insert into Tb_Region (RegionName,RegionSCName,RegionId,ParentId,AreaId) values (@RegionName,@RegionSCName,@RegionId,@ParentId,@AreaId)";
            MyDB.DBSetSelect(strSQL);
            MyDB.DBExcuteNonQuery();
            MyDB.DBClose();
        }

        public static bool IsExistCityInProvinceViaProvinceId(Guid regionId)
        {
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.AddParameters(regionId, "ParentId", SqlDbType.UniqueIdentifier);
            string strSQL = "select * from TB_Region where ParentId = @ParentId";
            MyDB.DBSetSelect(strSQL);
            if (MyDB.DBExecuteScalar() == null)
            {
                MyDB.DBClose();
                return false;
            }
            MyDB.DBClose();
            return true;
        }

        public static bool IsExistProvinceInOtherTable(Guid regionId)
        {
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.AddParameters(regionId, "RegionId", SqlDbType.UniqueIdentifier);
            string strSQL = "select * from TB_UserRegion where RegionId = @RegionId";
            MyDB.DBSetSelect(strSQL);
            if (MyDB.DBExecuteScalar() == null)
            {
                MyDB.DBClose();
            }
            else
            {
                MyDB.DBClose();
                return true;
            }
            MyDB.ClearParameters();
            MyDB.DBOpen();
            MyDB.AddParameters(regionId, "RegionId", SqlDbType.UniqueIdentifier);
            strSQL = "select * from TB_Region where ParentId = @RegionId";
            MyDB.DBSetSelect(strSQL);
            if (MyDB.DBExecuteScalar() == null)
            {
                MyDB.DBClose();
            }
            else
            {
                MyDB.DBClose();
                return true;
            }
            MyDB.ClearParameters();
            MyDB.DBOpen();
            MyDB.AddParameters(regionId, "RegionId", SqlDbType.UniqueIdentifier);
            strSQL = "select * from TB_CustomerBasicInfo where RegionId = @RegionId";
            MyDB.DBSetSelect(strSQL);
            if (MyDB.DBExecuteScalar() == null)
            {
                MyDB.DBClose();
            }
            else
            {
                MyDB.DBClose();
                return true;
            }
            MyDB.ClearParameters();
            MyDB.DBOpen();
            MyDB.AddParameters(regionId, "RegionId", SqlDbType.UniqueIdentifier);
            strSQL = "select * from TB_CompanyInfo where RegionId = @RegionId";
            MyDB.DBSetSelect(strSQL);
            if (MyDB.DBExecuteScalar() == null)
            {
                MyDB.DBClose();
            }
            else
            {
                MyDB.DBClose();
                return true;
            }
            MyDB.ClearParameters();
            return false;
        }

        public static bool IsExistSameProvinceName(string name, Guid areaId)
        {
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.AddParameters(name, "RegionName", SqlDbType.VarChar);
            MyDB.AddParameters(areaId, "AreaId", SqlDbType.UniqueIdentifier);
            string strSQL = "select * from tb_Region where RegionName = @RegionName and AreaId=@AreaId";
            MyDB.DBSetSelect(strSQL);
            if (MyDB.DBExecuteScalar() == null)
            {
                MyDB.DBClose();
                return false;
            }
            MyDB.DBClose();
            return true;
        }

        public static void RemoveProvinceViaId(Guid id)
        {
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.AddParameters(id, "RegionId", SqlDbType.UniqueIdentifier);
            string strSQL = "Delete From tb_Region where RegionId = @RegionId and RegionId <>  '00000000-0000-0000-0000-000000000000' ";
            MyDB.DBSetSelect(strSQL);
            MyDB.DBExcuteNonQuery();
            MyDB.DBClose();
        }

        public static void UpdateProvince(string name, string scName, Guid id)
        {
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.AddParameters(name, "RegionName", SqlDbType.VarChar);
            MyDB.AddParameters(scName, "RegionSCName", SqlDbType.VarChar);
            MyDB.AddParameters(id, "RegionId", SqlDbType.UniqueIdentifier);
            string strSQL = "Update tb_Region set RegionName = @RegionName,RegionSCName = @RegionSCName where RegionId = @RegionId";
            MyDB.DBSetSelect(strSQL);
            MyDB.DBExcuteNonQuery();
            MyDB.DBClose();
        }
    }
}

